﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class ChuyenKhauDAL
    {
        public static List<ChuyenKhauPublic> LoadChuyenKhauBySoCK(int SoChuyenKhau)
        {
            string chuoi = @"select [MaCK],[MaNK],[SoChuyenKhau],[ChuHo],[MaQH],[ThuongTru],[NoiChuyen],[LyDoChuyen],[NgayCap] ,ISNULL([CanBoNoiCap],0) as CanBoNoiCap ,[NguoiCap] ,[ChucVuNguoiCap] ,[NoiCap] ,ISNULL([CanBoNoiNhan],0)as CanBoNoiNhan ,ISNULL([NgayNhan],'') as NgayNhan,ISNULL([NguoiNhan],0)as NguoiNhan ,ISNULL([ChucVuNguoiNhan],0) as ChucVuNguoiNhan from ChuyenKhau where SoChuyenKhau=" + SoChuyenKhau + " and NoiCap in(select MaPX from ThietLap)";
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
            return ck.ToList();
        }
        public static List<ChuyenKhauPublic> LoadChuyenDenBySoCK(int SoChuyenKhau)
        {
            string chuoi = @"select [MaCK],[MaNK],[SoChuyenKhau],[ChuHo],[MaQH],[ThuongTru],[NoiChuyen],[LyDoChuyen],[NgayCap] ,ISNULL([CanBoNoiCap],0) as CanBoNoiCap ,[NguoiCap] ,[ChucVuNguoiCap] ,[NoiCap] ,ISNULL([CanBoNoiNhan],0)as CanBoNoiNhan ,ISNULL([NgayNhan],'') as NgayNhan,ISNULL([NguoiNhan],0)as NguoiNhan ,ISNULL([ChucVuNguoiNhan],0) as ChucVuNguoiNhan from ChuyenKhau where SoChuyenKhau=" + SoChuyenKhau + " and NoiCap not in(select MaPX from ThietLap)";
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
            return ck.ToList();
        }
        public static List<ChuyenKhauPublic> LoadChuyenKhauByMaNK(int MaNK)
        {
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(@"select [MaCK],[MaNK],[SoChuyenKhau],[ChuHo],[MaQH],[ThuongTru],[NoiChuyen],[LyDoChuyen],[NgayCap] ,ISNULL([CanBoNoiCap],0) as CanBoNoiCap ,[NguoiCap] ,[ChucVuNguoiCap] ,[NoiCap] ,ISNULL([CanBoNoiNhan],0)as CanBoNoiNhan ,ISNULL([NgayNhan],'') as NgayNhan,ISNULL([NguoiNhan],0)as NguoiNhan ,ISNULL([ChucVuNguoiNhan],0) as ChucVuNguoiNhan from ChuyenKhau where MaNK=" + MaNK);
            return ck.ToList();
        }
        public static int CountChuyenDiByYear(int firstyear, int lastyear)
        {
            var cd = Common.db.ExecuteStoreQuery<int>(@"select count(*) from ChuyenKhau where left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) in (select MaPX from ThietLap) and year(NgayNhan)>=" + firstyear + "and year(NgayNhan)<=" + lastyear);
            return cd.FirstOrDefault();
        }
        public static int CountChuyenDenByYear(int firstyear, int lastyear)
        {
            var cden = Common.db.ExecuteStoreQuery<int>(@"select count(*) from ChuyenKhau where left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) not in (select MaPX from ThietLap) and year(NgayNhan)>=" + firstyear + "and year(NgayNhan)<=" + lastyear);
            return cden.FirstOrDefault();
        }

        public static int LoadMaCK()
        {
            var ck = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(MaCK),0) as MaCK from ChuyenKhau");
            return ck.FirstOrDefault();
        }
       
        public static void InsertChuyenDen(ChuyenKhauPublic _cden)
        {
            ChuyenKhau ck = new ChuyenKhau();
            ck.CanBoNoiCap = _cden.CanBoNoiCap;
            ck.CanBoNoiNhan = _cden.CanBoNoiNhan;
            ck.ChucVuNguoiCap = _cden.ChucVuNguoiCap;
            ck.ChucVuNguoiNhan = _cden.ChucVuNguoiNhan;
            ck.NguoiCap = _cden.NguoiCap;
            ck.NguoiNhan = _cden.NguoiNhan;

            ck.LyDoChuyen = _cden.LyDoChuyen;
            ck.MaCK = _cden.MaCK;
            ck.MaNK = _cden.MaNK;
            ck.NgayCap = _cden.NgayCap;
            ck.NgayNhan = _cden.NgayNhan;
            
            //ck.NgayChuyen = _cden.NgayChuyen;
            //ck.NgayDen = _cden.NgayDen;
            ck.ThuongTru = _cden.ThuongTru;
            ck.NoiCap = _cden.NoiCap;
            ck.NoiChuyen = _cden.NoiChuyen;
            ck.SoChuyenKhau = _cden.SoChuyenKhau;
            ck.ChuHo = _cden.ChuHo;
            ck.MaQH = _cden.MaQH;
            Common.db.AddToChuyenKhaus(ck);
            Common.db.SaveChanges();
        }
        public static int LoadSoChuyenKhau()
        {
            var ck = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(SoChuyenKhau),0) from ChuyenKhau 
                                                        where LEFT(RIGHT('0'+cast(SoChuyenKhau as varchar(8)),8),2) in (select right(MaPX,2) as MaPX from ThietLap)");
            return ck.FirstOrDefault();
        }
        public static int CountChuyenDinByYear(int firstyear, int lastyear)
        {
            var cden = Common.db.ExecuteStoreQuery<int>(@"select count(*) from ChuyenKhau where left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) in (select MaPX from ThietLap) and year(NgayNhan)>=" + firstyear + "and year(NgayNhan)<=" + lastyear+"and Len(NoiChuyen)=6");
            return cden.FirstOrDefault();
        }
//        public static bool TestChuyenDinByMaNK()
//        {
//            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(@"
//                                                                select * from ChuyenKhau where LEFT(right('0'+MaCK,8),2)=(select MaPX from ThietLap) and LEFT(right('0'+NoiChuyen,2),2)!=(select MaPX from ThietLap)  and MaNK=1000001");
//        }
        public static void UpdateChuyenDen(int SoChuyenKhau, byte MaQH,string ChuHo, string ThuongTru, string LyDoChuyen, DateTime NgayCap, string NguoiCap, byte ChucVu, int NoiCap, string NguoiNhan, byte ChucVuNguoiNhan, int MaCK)
        {
            string chuoi = "update ChuyenKhau set SoChuyenKhau=" + SoChuyenKhau +",MaQH=" + MaQH + ",ChuHo='"+ChuHo+"',ThuongTru=N'" + ThuongTru + "',LyDoChuyen=N'" + LyDoChuyen + "',NgayCap='" + NgayCap + "',NguoiCap=N'" + NguoiCap + "',ChucVuNguoiCap=" + ChucVu + ",NoiCap=" + NoiCap + ",NguoiNhan=N'" + NguoiNhan + "',ChucVuNguoiNhan=" + ChucVuNguoiNhan + " where MaCK=" + MaCK;
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
            Common.db.SaveChanges();
        }
        public static void DeleteChuyenKhau(int MaCK)
        {
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>("delete  from ChuyenKhau where MaCK=" + MaCK);
            Common.db.SaveChanges();
        }
        public static void DeleteChuyenKhauNK(int MaNK)
        {
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>("delete  from ChuyenKhau where MaNK=" + MaNK);
            Common.db.SaveChanges();
        }

        public static void UpdateChuyenDi(byte MaQH, string ChuHo, int NoiChuyen, string LyDoChuyen, string NguoiCap, byte ChucVu, string NguoiNhan, byte ChucVuNguoiNhan, int MaCK)
        {
            string chuoi = "update ChuyenKhau set MaQH=" + MaQH + ",ChuHo=N'" + ChuHo + "',NoiChuyen=" + NoiChuyen + ",LyDoChuyen=N'" + LyDoChuyen + "',NguoiCap=N'" + NguoiCap + "',ChucVuNguoiCap=" + ChucVu + ",NguoiNhan=N'"+NguoiNhan+"',ChucVuNguoiNhan="+ChucVuNguoiNhan+" where MaCK=" + MaCK;
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
            Common.db.SaveChanges();
        }
        //public static List<ChuyenKhauPublic> LoadChuyenKhauBySoCK(int SoChuyenKhau)
        //{
        //    string chuoi = @"select [MaNK],[SoChuyenKhau],[ChuHo],[MaQH],[ThuongTru],[NoiChuyen],[LyDoChuyen],[NgayCap] ,ISNULL([CanBoNoiCap],0) as CanBoNoiCap ,[NguoiCap] ,[ChucVuNguoiCap] ,[NoiCap] ,ISNULL([CanBoNoiNhan],0)as CanBoNoiNhan ,ISNULL([NgayNhan],'') as NgayNhan,ISNULL([NguoiNhan],0)as NguoiNhan ,ISNULL([ChucVuNguoiNhan],0) as ChucVuNguoiNhan from ChuyenKhau where SoChuyenKhau=" + SoChuyenKhau + " and left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) in(select right(MaPX,2) from ThietLap)";
        //    var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
        //    return ck.ToList();
        //}
        public static bool TestChuyenKhau(int MaNK)
        {
            string chuoi = "select MaNK from ChuyenKhau where NoiCap in (select MaPX from ThietLap) and LEN(NoiChuyen)<7 and MaNK="+MaNK;
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
            if (ck.Count() > 0)
                return false;
            else
                return true;
        }

        public static int CountChuyenKhauNKByDay(DateTime batdau, DateTime ketthuc)
        {
            var ck = Common.db.ExecuteStoreQuery<int>("select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6");
            return ck.FirstOrDefault();
        }
        public static int CountChuyenKhauNKNuByDay(DateTime batdau, DateTime ketthuc)
        {
            var ck = Common.db.ExecuteStoreQuery<int>("select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6 and MaNK in (select MaNK from Nhankhau where GioiTinh=0)");
            return ck.FirstOrDefault();
        }
        public static int CountChuyenKhauNK14ByDay(DateTime batdau, DateTime ketthuc)
        {
            var ck = Common.db.ExecuteStoreQuery<int>("select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6 and MaNK in (select MaNK from Nhankhau where DATEDIFF(yyyy,NgaySinh,NgayNhap)>14)");
            return ck.FirstOrDefault();
        }
        //tỉnh
        public static int CountChuyenKhauNKTinhByDay(DateTime batdau, DateTime ketthuc)
        {
            var ck = Common.db.ExecuteStoreQuery<int>("select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6 and LEFT(right('0'+ CAST(NoiChuyen as varchar(6)),6),2) not in (select LEFT(right('0'+ cast(MaPX as varchar(6)),6),2) from ThietLap) ");
            return ck.FirstOrDefault();
        }
        public static int CountChuyenKhauNKTinhNuByDay(DateTime batdau, DateTime ketthuc)
        {
            var ck = Common.db.ExecuteStoreQuery<int>("select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6 and LEFT(right('0'+ CAST(NoiChuyen as varchar(6)),6),2) not in (select LEFT(right('0'+ cast(MaPX as varchar(6)),6),2) from ThietLap) and MaNK in (select MaNK from Nhankhau where GioiTinh=0)");
            return ck.FirstOrDefault();
        }
        public static int CountChuyenKhauNKTinh14ByDay(DateTime batdau, DateTime ketthuc)
        {
            var ck = Common.db.ExecuteStoreQuery<int>("select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6 and LEFT(right('0'+ CAST(NoiChuyen as varchar(6)),6),2) not in (select LEFT(right('0'+ cast(MaPX as varchar(6)),6),2) from ThietLap) and MaNK in (select MaNK from Nhankhau where DATEDIFF(yyyy,NgaySinh,NgayNhap)>14 )");
            return ck.FirstOrDefault();
        }
        //huyện
        public static int CountChuyenKhauNKHuyenByDay(DateTime batdau, DateTime ketthuc)
        {
            string chuoi = @"select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6 and LEFT(right('0'+ CAST(NoiChuyen as varchar(6)),6),2) in (select LEFT(right('0'+ cast(MaPX as varchar(6)),6),2) from ThietLap) and SUBSTRING(right('0'+ CAST(NoiChuyen as varchar(6)),6),2,2) not in (select SUBSTRING(right('0'+ cast(MaPX as varchar(6)),6),2,2) from ThietLap)";
            var ck = Common.db.ExecuteStoreQuery<int>(chuoi);
            return ck.FirstOrDefault();
        }
        public static int CountChuyenKhauNKHuyenNuByDay(DateTime batdau, DateTime ketthuc)
        {
            var ck = Common.db.ExecuteStoreQuery<int>("select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6 and LEFT(right('0'+ CAST(NoiChuyen as varchar(6)),6),2) in (select LEFT(right('0'+ cast(MaPX as varchar(6)),6),2) from ThietLap) and MaNK in (select MaNK from Nhankhau where GioiTinh=0) and SUBSTRING(right('0'+ CAST(NoiChuyen as varchar(6)),6),2,2) not in (select SUBSTRING(right('0'+ cast(MaPX as varchar(6)),6),2,2) from ThietLap)");
            return ck.FirstOrDefault();
        }
        public static int CountChuyenKhauNKHuyen14ByDay(DateTime batdau, DateTime ketthuc)
        {
            var ck = Common.db.ExecuteStoreQuery<int>("select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6 and LEFT(right('0'+ CAST(NoiChuyen as varchar(6)),6),2) in (select LEFT(right('0'+ cast(MaPX as varchar(6)),6),2) from ThietLap) and MaNK in (select MaNK from Nhankhau where DATEDIFF(yyyy,NgaySinh,NgayNhap)>14 ) and SUBSTRING(right('0'+ CAST(NoiChuyen as varchar(6)),6),2,2) not in (select SUBSTRING(right('0'+ cast(MaPX as varchar(6)),6),2,2) from ThietLap)");
            return ck.FirstOrDefault();
        }
        //xã
        public static int CountChuyenKhauNKXaByDay(DateTime batdau, DateTime ketthuc)
        {
            string chuoi = @"select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6 and LEFT(right('0'+ CAST(NoiChuyen as varchar(6)),6),4) in (select LEFT(right('0'+ cast(MaPX as varchar(6)),6),4) from ThietLap)";
            var ck = Common.db.ExecuteStoreQuery<int>(chuoi);
            return ck.FirstOrDefault();
        }
        public static int CountChuyenKhauNKXaNuByDay(DateTime batdau, DateTime ketthuc)
        {
            var ck = Common.db.ExecuteStoreQuery<int>("select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6 and LEFT(right('0'+ CAST(NoiChuyen as varchar(6)),6),4) in (select LEFT(right('0'+ cast(MaPX as varchar(6)),6),4) from ThietLap) and MaNK in (select MaNK from Nhankhau where GioiTinh=0)");
            return ck.FirstOrDefault();
        }
        public static int CountChuyenKhauNKXa14ByDay(DateTime batdau, DateTime ketthuc)
        {
            var ck = Common.db.ExecuteStoreQuery<int>("select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'and len(NoiChuyen)<=6 and LEFT(right('0'+ CAST(NoiChuyen as varchar(6)),6),4) in (select LEFT(right('0'+ cast(MaPX as varchar(6)),6),4) from ThietLap) and MaNK in (select MaNK from Nhankhau where DATEDIFF(yyyy,NgaySinh,NgayNhap)>14 )");
            return ck.FirstOrDefault();
        }

        public static int CountTachKhauByDay(DateTime batdau, DateTime ketthuc)
        {
            var ck = Common.db.ExecuteStoreQuery<int>("select count(MaCK) from ChuyenKhau where LyDoChuyen like N'Tách lập hộ mới'");
            return ck.FirstOrDefault();
        }
        public static int CountCKNgoaiTinhByDay(DateTime batdau, DateTime ketthuc)
        {
            string chuoi = @"select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "' and NgayCap<='" + ketthuc + "' and NoiChuyen in (select MaPX from ThietLap) and left(right('0'+cast(NoiCap as varchar(6)),6),2) not in (select Left(right('0'+cast(MaPX as varchar(6)),6),2) from ThietLap)";
            var ck = Common.db.ExecuteStoreQuery<int>(chuoi);
            return ck.FirstOrDefault();
        }
        public static int CountSoCKByDay(DateTime batdau, DateTime ketthuc)
        {
            string chuoi = @"select COUNT(distinct nk.MaSHK) from Nhankhau nk, ChuyenKhau ck where nk.MaNK=ck.MaNK and ck.NgayCap>='"+batdau+"' and ck.NgayCap<='"+ketthuc+"' and NoiChuyen not in (select MaPX from ThietLap) and len(NoiChuyen)<=6";
            var ck = Common.db.ExecuteStoreQuery<int>(chuoi);
            return ck.FirstOrDefault();
        }
        public static int CountChuyenDiByDay(DateTime batdau, DateTime ketthuc)
        {
            string chuoi = @"select count(MaCK) from ChuyenKhau where NgayCap>='" + batdau + "' and NgayCap<='" + ketthuc + "' and NoiChuyen not in (select MaPX from ThietLap) and len(NoiChuyen)<=6";
            var ck = Common.db.ExecuteStoreQuery<int>(chuoi);
            return ck.FirstOrDefault();
        }
        public static ChuyenKhauPublic TimChuyenKhau(int SoChuyenKhau,string tenserver,string tendata)
        {
           
                string chuoi = @"select [MaCK],[MaNK],[SoChuyenKhau],[ChuHo],[MaQH],[ThuongTru],[NoiChuyen],[LyDoChuyen],[NgayCap] ,ISNULL([CanBoNoiCap],0) as CanBoNoiCap ,[NguoiCap] ,[ChucVuNguoiCap] ,[NoiCap] ,ISNULL([CanBoNoiNhan],0)as CanBoNoiNhan ,ISNULL([NgayNhan],'') as NgayNhan,ISNULL([NguoiNhan],0)as NguoiNhan ,ISNULL([ChucVuNguoiNhan],0) as ChucVuNguoiNhan from ['" + tenserver + "].[" + tendata + "].[dbo].[ChuyenKhau] where SoChuyenKhau=" + SoChuyenKhau;
                var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(chuoi);
                return ck.FirstOrDefault();
            
        }
    }
}
